<?php
/**
 * @author SilverBiology, LLC
 */
include_once('class.dbconnect.php');
include_once('../../config.php');

class SpeciesOccurrence {
	protected $db;
	public $record;
	public $totalCount;

	function __construct() {
		$this->db = new DbConnect(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME);
	}

	public function sendEmail($id, $auth) {
		$approve = BASE_URL . 'resources/api/api.php?cmd=updateStatus&output=html&id=' . $id . '&stage=Approved&auth=' . $auth;
		$decline = BASE_URL . 'resources/api/api.php?cmd=updateStatus&output=html&id=' . $id . '&stage=Declined&auth=' . $auth;

		$message = "<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" /><title>3 X 5 Card</title></head><body>";
		$message .= $this->getCard($id, 0, true);
		$message .= sprintf('<center><a href = "%s">Approve</a>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href = "%s">Decline</a></center></body></html>'
			,	$approve
			,	$decline
		);
		$subject = 'New Species Observation Card';
		$headers = "From:" . REPLY_EMAIL . " \nContent-Type: text/html; charset=utf-8";
		mail(NEW_OCCURRENCE_EMAIL, $subject, $message, $headers);
		mail("mikegiddens@yahoo.com", $subject, $message, $headers);
	}

	public function listData($dir, $sort, $filter, $public, $limit, $start) {
		$this->db->open();
		$where = '';
		$posts = array();
		if(!empty($filter)) {
			$query = "SELECT o.*, s.scientificName as scientificName FROM occurrence o LEFT OUTER JOIN species_aou s ON o.vernacularName = s.commonName WHERE 1=1 ";
			$coutQuery = "SELECT COUNT(*) as ct FROM occurrence WHERE id = id ";
			foreach($filter as $list)  {
				if ($list['type'] == 'string') {
					$where .= sprintf(" AND %s LIKE '%s'"
						,	mysql_escape_string($list['field'])
						,	mysql_escape_string($list['value'].'%')
					);
				}
				if ($list['type'] == 'integer') {
					$where .= sprintf(" AND %s = '%s'"
						,	mysql_escape_string($list['field'])
						,	mysql_escape_string($list['value'])
					);
				}
				if ($list['type'] == 'date') {
					$newDate = date("Y-m-d", strtotime($list['value']));
					if($list['comparison'] == 'lt') {
						$where .= sprintf(" AND %s < '%s'"
							,	mysql_escape_string($list['field'])
							,	mysql_escape_string($newDate)
						);
					}
					if($list['comparison'] == 'gt') {
						$where .= sprintf(" AND %s > '%s'"
							,	mysql_escape_string($list['field'])
							,	mysql_escape_string($newDate)
						);
					}
					if($list['comparison'] == 'eq') {
						$where .= sprintf(" AND %s LIKE '%s%%'"
							,	mysql_escape_string($list['field'])
							,	mysql_escape_string($newDate)
						);
					}
				}
				if ($list['type'] == 'boolean') {
					if ($list['value'] == 'true') {
						$where .= sprintf(" AND %s = '1'"
							,	mysql_escape_string($list['field'])
						);
					} else {
						$where .= sprintf(" AND %s != '1'"
							,	mysql_escape_string($list['field'])
						);
					}
				}
				if ($list['type'] == 'list' ) {
					$arr = explode(",", $list['value']);
     				switch (count($arr)) {
     					case 1:
								if ($arr[0] == "Form") $arr[0] = '0';
								if ($arr[0] == "Card") $arr[0] = '1';
     						$where .= sprintf(" AND %s IN ( '%s')"
									,	mysql_escape_string($list['field'])
									,	mysql_escape_string($arr[0])
								);
     					break;

     					case 2:
     						$where .= sprintf(" AND %s IN ( '%s', '%s')"
									,	mysql_escape_string($list['field'])
									,	mysql_escape_string($arr[0])
									,	mysql_escape_string($arr[1])
								);
	     					break;
							
     					case 3:
								$where .= sprintf(" AND %s IN ( '%s', '%s', '%s' )"
									,	mysql_escape_string($list['field'])
									,	mysql_escape_string($arr[0])
									,	mysql_escape_string($arr[1])
									,	mysql_escape_string($arr[2])
								);
								break;
					}
				}

			}
			if($public == 'true'){
				$where .= " AND stage = 'Approved'" ;
			}
			$where .= " AND stage !='Queue' ";
			$query .= $where;
			$coutQuery .= $where;

			$query .= sprintf(" ORDER BY %s %s"
					,	mysql_escape_string($sort)
					,	mysql_escape_string($dir)
			);

			$query .= sprintf(" LIMIT %s, %s"
					,	mysql_escape_string($start)
					,	mysql_escape_string($limit)
			);
			
		} else {
			if ($public == 'true') {
				$query = sprintf("SELECT o.*, s.scientificName as scientificName FROM `occurrence` o LEFT OUTER JOIN species_aou s ON o.vernacularName = s.commonName WHERE stage = 'Approved' ORDER BY %s %s  LIMIT %s , %s"
					,	mysql_escape_string($sort)
					,	mysql_escape_string($dir)
					,	mysql_escape_string($start)
					,	mysql_escape_string($limit)
				);

				$coutQuery = sprintf("SELECT COUNT(*) as ct FROM `occurrence` WHERE stage = 'Approved' ORDER BY %s %s"
					,	mysql_escape_string($sort)
					,	mysql_escape_string($dir)
				);
			} else {
				$query = sprintf("SELECT o.*, s.scientificName as scientificName FROM `occurrence` o LEFT OUTER JOIN species_aou s ON o.vernacularName = s.commonName WHERE stage != 'Queue' ORDER BY %s %s LIMIT %s , %s"
					,	mysql_escape_string($sort)
					,	mysql_escape_string($dir)
					,	mysql_escape_string($start)
					,	mysql_escape_string($limit)
				);

				$coutQuery = sprintf("SELECT COUNT(*) as ct FROM `occurrence` WHERE stage != 'Queue' ORDER BY %s %s"
					,	mysql_escape_string($sort)
					,	mysql_escape_string($dir)
				);
			}
		}
#print $coutQuery;
		$cout = @mysql_query($coutQuery);
		$rowCount = @mysql_fetch_array($cout);
		$this->totalCount = $rowCount['ct'];
		$result = @mysql_query($query);
#print $query;		
		//$this->totalCount = @mysql_num_rows($result);
		$scientificName = '';

		if($result) {
			while($row = @mysql_fetch_row($result, MYSQL_ASSOC)) {
				if ($_SESSION['accessType'] != "ADMIN" && $_SESSION['accessType'] != "MANAGER" ) {
					unset($row["auth"]);
				}
				$posts[] = $row;
			}
			$this->db->close();
			return ($posts);
		} else {
			return (false);
		}
	}

	public function showList($like, $type ) {
		$this->db->open();
		switch($type) {
			case 'locality':
				$limit = 20;
				$query = sprintf("SELECT DISTINCT name, admin3_code FROM geonames WHERE name LIKE '%s%%' LIMIT %s"
					,	mysql_escape_string($like)
					,	mysql_escape_string($limit)
				);

				$result = @mysql_query($query);
				if($result){
					while($row = @mysql_fetch_array($result)){
						$name = $row['name'];
						$county = $row['admin3_code'];
						$posts[] = array('name'=> $name, 'county'=> $county);
					}
					$this->db->close();
					return ($posts);
				} else {


					return (false);
				}
				break;

			case 'parish':

				$query = sprintf("SELECT DISTINCT admin3_code FROM geonames ORDER BY admin3_code ASC");
				$result = @mysql_query($query);
				if($result){
					while($row = @mysql_fetch_array($result)){
						$name = $row['admin3_code'];
						if($name != '')
							$posts[] = array('name'=> $name);
						}
						$this->db->close();
						return ($posts);
					} else {
						return (false);
					}
					break;

		case 'vernacular':
			$limit = 6;
			
			$query = sprintf("SELECT * FROM species_aou WHERE commonName LIKE '%s%%' ORDER BY commonName LIMIT %s"
				, mysql_escape_string($like)
				, mysql_escape_string($limit)
			);

			$result = @mysql_query($query);
			if($result){
				while($row = @mysql_fetch_array($result)){
					$order = $row['order_'];
					$family = $row['family'];
					$species = $row['scientificName'];
					$vernacular = $row['commonName'];
					$posts[] = array('match' => 'exact', 'order'=> $order, 'family'=> $family, 'species'=> $species, 'name'=> $vernacular);
				}
			}

			$query = sprintf("SELECT * FROM species_aou WHERE commonName LIKE '%%%s%%' AND commonName NOT LIKE '%s%%' ORDER BY commonName LIMIT %s"
				, mysql_escape_string($like)
				, mysql_escape_string($like)
				, mysql_escape_string($limit)
			);

			$result = @mysql_query($query);
			if($result){
				while($row = @mysql_fetch_array($result)){
					$order = $row['order_'];
					$family = $row['family'];
					$species = $row['scientificName'];
					$vernacular = $row['commonName'];
					$posts[] = array('match' => 'exact', 'order'=> $order, 'family'=> $family, 'species'=> $species, 'name'=> $vernacular);
				}
			}

			$query = sprintf("SELECT * FROM species_aou WHERE scientificName LIKE '%s%%' ORDER BY scientificName LIMIT %s"
				, mysql_escape_string($like)
				, mysql_escape_string($limit)
			);

			$result = @mysql_query($query);
			if($result){
#			if(mysql_num_rows($result) > 0){
				while($row = @mysql_fetch_array($result)){
					$order = $row['order_'];
					$family = $row['family'];
					$species = $row['scientificName'];
					$vernacular = $row['commonName'];
					$posts[] = array('match' => 'exact', 'order'=> $order, 'family'=> $family, 'species'=> $species, 'name'=> $vernacular);
				}
			}

			$this->db->close();
			return ($posts);
			break;

		case 'vernacular_old':
			$limit = 6;
			$query = sprintf("SELECT * FROM species WHERE vernacular LIKE '%s%%' AND louisiana = 1 ORDER BY vernacular LIMIT %s"
				, mysql_escape_string($like)
				, mysql_escape_string($limit)
			);

			$result = @mysql_query($query);
			//$this->totalCount = @mysql_num_rows($result);
			if($result){
				while($row = @mysql_fetch_array($result)){
					$id = $row['id'];
					$order = $row['order_'];
					$family = $row['family'];
					$familyVernacular = $row['familyVernacular'];
					$genus = $row['genus'];
					$species = $row['species'];
					$vernacular = $row['vernacular'];
					$breeding = $row['breeding'];
					$breedingVerbarim = $row['breedingVerbarim'];
					$posts[] = array('match' => 'exact', 'id'=> $id, 'order'=> $order, 'family'=> $family, 'familyVernacular'=> $familyVernacular, 'genus'=> $genus, 'species'=> $species, 'name'=> $vernacular, 'breeding'=> $breeding, 'breedingVerbarim'=> $breedingVerbarim);
				}
			}

			$query = sprintf("SELECT * FROM species WHERE vernacular LIKE '%%%s%%' AND louisiana = 1 ORDER BY vernacular LIMIT %s"
				, mysql_escape_string($like)
				, mysql_escape_string($limit)
			);

			$result = @mysql_query($query);
			//$this->totalCount = @mysql_num_rows($result);
			if($result){
				while($row = @mysql_fetch_array($result)){
					$id = $row['id'];
					$order = $row['order_'];
					$family = $row['family'];
					$familyVernacular = $row['familyVernacular'];
					$genus = $row['genus'];
					$species = $row['species'];
					$vernacular = $row['vernacular'];
					$breeding = $row['breeding'];
					$breedingVerbarim = $row['breedingVerbarim'];
					$posts[] = array('match' => 'near', 'id'=> $id, 'order'=> $order, 'family'=> $family, 'familyVernacular'=> $familyVernacular, 'genus'=> $genus, 'species'=> $species, 'name'=> $vernacular, 'breeding'=> $breeding, 'breedingVerbarim'=> $breedingVerbarim);
				}
			}

			$query = sprintf("SELECT * FROM species WHERE vernacular LIKE '%s%%' AND louisiana = 0 ORDER BY vernacular LIMIT %s"
				, mysql_escape_string($like)
				, mysql_escape_string($limit)
			);

			$result = @mysql_query($query);
			//$this->totalCount = @mysql_num_rows($result);

			if(mysql_num_rows($result) > 0){
				while($row = @mysql_fetch_array($result)){
					$id = $row['id'];
					$order = $row['order_'];
					$family = $row['family'];
					$familyVernacular = $row['familyVernacular'];
					$genus = $row['genus'];
					$species = $row['species'];
					$vernacular = $row['vernacular'];
					$breeding = $row['breeding'];
					$breedingVerbarim = $row['breedingVerbarim'];
					$posts[] = array('match' => 'other', 'id'=> $id, 'order'=> $order, 'family'=> $family, 'familyVernacular'=> $familyVernacular, 'genus'=> $genus, 'species'=> $species, 'name'=> $vernacular, 'breeding'=> $breeding, 'breedingVerbarim'=> $breedingVerbarim);
				}
			} else {
			}

				$query = sprintf("SELECT * FROM species WHERE vernacular LIKE '%%%s%%' AND louisiana = 0 ORDER BY vernacular LIMIT %s"
					, mysql_escape_string($like)
					, mysql_escape_string($limit)
				);
	
				$result = @mysql_query($query);
				//$this->totalCount = @mysql_num_rows($result);
				if($result){
					while($row = @mysql_fetch_array($result)){
						$id = $row['id'];
						$order = $row['order_'];
						$family = $row['family'];
						$familyVernacular = $row['familyVernacular'];
						$genus = $row['genus'];
						$species = $row['species'];
						$vernacular = $row['vernacular'];
						$breeding = $row['breeding'];
						$breedingVerbarim = $row['breedingVerbarim'];
						$posts[] = array('match' => 'other', 'id'=> $id, 'order'=> $order, 'family'=> $family, 'familyVernacular'=> $familyVernacular, 'genus'=> $genus, 'species'=> $species, 'name'=> $vernacular, 'breeding'=> $breeding, 'breedingVerbarim'=> $breedingVerbarim);
					}
				}				

			$this->db->close();
			return ($posts);
			break;
			
			
		}
	}

	public function updateStatus($id, $stage, $auth, $public = 0)	{
		$this->db->open();
		$query = sprintf("UPDATE occurrence SET stage = '%s', public = '%s' WHERE id = '%s' AND auth = '%s' "
			, mysql_escape_string($stage)
			, mysql_escape_string($public)
			, mysql_escape_string($id)
			, mysql_escape_string($auth)
		);

		$result = @mysql_query($query);

		if ($result) {
			return(true);
		} else {
			return(false);
		}
	}

	function updateMarkedPrinted($id) {
		$this->db->open();
		$query = sprintf("UPDATE occurrence SET markedPrinted = 1 WHERE id = '%s' "
			, mysql_escape_string($id)
		);
		$result = @mysql_query($query);
		if (mysql_affected_rows() > 0) {
			return(true);
		} else {
			return(false);
		}
	}

	public function getCsvFile($file, $filter, $public) {
		$this->db->open();
		$table = "occurrence";
		if(!empty($filter)) {

			$query = "SELECT DISTINCT(concat(genus, ' ', species)) AS scientificName, occurrence.id, vernacularName, numberSeen, sex, age, characteristics, state, dateObserved, verbatimDate,	locality, description, stage, lastModified, observers, contactInfo,county, significance, audioRec, audioRecUri, heardOnly, photo, photoUri, sightRec, specimen, video, videoUri, markedPrinted from occurrence, species WHERE occurrence.vernacularName = species.vernacular AND stage != 'QUEUE'";
			foreach($filter as $list)  {
				if ($list['type'] == 'string') {
					$where .= sprintf(" AND %s LIKE '%s'"
						,	mysql_escape_string($list['field'])
						,	mysql_escape_string($list['value'].'%')
					);
				}
				if ($list['type'] == 'date') {
					$newDate = date("Y-m-d", strtotime($list['value']));
					if($list['comparison'] == 'lt') {
						$where .= sprintf(" AND %s < '%s'"
							,	mysql_escape_string($list['field'])
							,	mysql_escape_string($newDate)
						);
					}
					if($list['comparison'] == 'gt') {
						$where .= sprintf(" AND %s > '%s'"
							,	mysql_escape_string($list['field'])
							,	mysql_escape_string($newDate)
						);
					}
					if($list['comparison'] == 'eq') {
						$where .= sprintf(" AND %s LIKE '%s%%'"
							,	mysql_escape_string($list['field'])
							,	mysql_escape_string($newDate)
						);
					}
				}
				if ($list['type'] == 'boolean') {
					if ($list['value'] == 'true') {
						$where .= sprintf(" AND %s = '1'"
							,	mysql_escape_string($list['field'])
						);
					} else {
						$where .= sprintf(" AND %s != '1'"
							,	mysql_escape_string($list['field'])
						);
					}
				}
				if ($list['type'] == 'list' ) {
					$arr = explode(",", $list['value']);
     				switch (count($arr)) {
     					case 1:
     						$where .= sprintf(" AND %s IN ( '%s')"
									,	mysql_escape_string($list['field'])
									,	mysql_escape_string($arr[0])
								);
	     					break;

     					case 2:
     						$where .= sprintf(" AND %s IN ( '%s', '%s')"
									,	mysql_escape_string($list['field'])
									,	mysql_escape_string($arr[0])
									,	mysql_escape_string($arr[1])
								);
	     					break;

     					case 3:
								$where .= sprintf(" AND %s IN ( '%s', '%s', '%s' )"
									,	mysql_escape_string($list['field'])
									,	mysql_escape_string($arr[0])
									,	mysql_escape_string($arr[1])
									,	mysql_escape_string($arr[2])
								);
								break;
					}
				}

			}
			if($public == 'true'){
				$where .= " AND stage = 'Approved'" ;
			}
			$query .= $where;
		} else {
			$query = "SELECT DISTINCT(concat(genus, ' ', species)) AS scientificName, occurrence.id,	vernacularName, numberSeen,	sex, age, characteristics, state, dateObserved, verbatimDate,	locality, description, stage, lastModified, observers, contactInfo,county, significance, audioRec, audioRecUri, heardOnly, photo, photoUri, sightRec, specimen, video, videoUri, markedPrinted from occurrence, species WHERE occurrence.vernacularName = species.vernacular AND stage != 'QUEUE'";
#			if($public == 'true'){
				$where = " AND stage = 'Approved'" ;
#			}
			$query .= $where;
		}

		$out = '';
		$result = @mysql_query($query);
		$f = fopen($file, 'w');

		$header = array("scientificName", "id", "vernacularName", "numberSeen", "sex", "age", "characteristics", "state", "dateObserved", "verbatimDate",	"locality", "description", "stage", "lastModified", "observers", "contactInfo", "county", "significance", "audioRec", "audioRecUri", "heardOnly", "photo", "photoUri", "sightRec", "specimen", "video", "videoUri");
		fputcsv($f, $header);
		while ($l = @mysql_fetch_array($result, MYSQL_ASSOC)) {
			unset($l["markedPrinted"]);
			fputcsv($f, $l);
		}

		fclose($f);
	}

	public function buildDWCA($file, $filter, $public) {
		$csvFile = sys_get_temp_dir() . '/' . $file . '.csv';
		$filename = sys_get_temp_dir() . '/' . $file . '.zip';
 		$tmpFile = $file . '.csv';
 		$zip = new ZipArchive();

		if ($zip->open($filename, ZIPARCHIVE::CREATE) == TRUE) {
			$this->getCsvFile($csvFile, $filter, $public);
			$zip->addFile($csvFile, $tmpFile);
			$zip->close();
			return (true);
		} else {
			return (false);
		}
	}

	public function getCard($id, $forcePrint=0, $contact=false) {
		$this->db->open();
		if ($id == 'all') {
			$query = sprintf("SELECT o.*, s.scientificName as scientificName FROM occurrence o LEFT OUTER JOIN species_aou s ON o.vernacularName = s.commonName WHERE markedPrinted = 0 AND stage != 'QUEUE'" );
		} else {
			if ($forcePrint) {
				$query = sprintf("SELECT o.*, s.scientificName as scientificName FROM occurrence o LEFT OUTER JOIN species_aou s ON o.vernacularName = s.commonName WHERE id = '%s'"
					,	mysql_escape_string($id)
				);
			} else {
				$query = sprintf("SELECT o.*, s.scientificName as scientificName FROM occurrence o LEFT OUTER JOIN species_aou s ON o.vernacularName = s.commonName WHERE id = '%s' AND markedPrinted = 0 "
					,	mysql_escape_string($id)
				);
			}
		}

		$result = @mysql_query($query);
		$cnt = 0;
		$html = "";
/*		
		$html = "<html>"
		. "<head>"
		. "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />"
		. "<title>3 X 5 Card</title>"
		. "</head>"
		. "<body>";
*/		
		if($result){
			while($row = @mysql_fetch_array($result)){
				$detection = '';
				if($row['audioRec'] == '1')
					$detection = 'Audio Rec, ';
				if($row['heardOnly'] == '1')
					$detection .= 'Heard Only, ';
				if($row['photo'] == '1')
					$detection .= 'Photo, ';
				if($row['sightRec'] == '1')
					$detection .= 'Sight Rec, ';
				if($row['specimen'] == '1')
					$detection .= 'Specimen, ';
				if($row['video'] == '1')
					$detection .= 'Video';

				$tpl = file_get_contents("../tpl/card.html");
  			$html .= sprintf($tpl
					,	$row['vernacularName']
					,	($row['scientificName'] == '') ? '' : '(' . $row['scientificName'] . ')'
					, $row['age']
					,	$row['sex']
					,	$row['characteristics']
					,	$row['numberSeen']
					,	$row['county']
					,	$row['locality']
					,	date("d M Y", strtotime($row['dateObserved']))
					,	$row['verbatimDate']
					,	$row['observers']
					,	$detection
					,	$row['description']
					,	$row['significance']
					, $row['id']
					,	date("d M Y")
				);
				
				if ($contact && $row['contactInfo'] != '') {
					$html .= "<br/><b>Contact:</b> " . $row['contactInfo'] . "<br/>";
				}

				$cnt++;
				if(($cnt%2) == 0)	{
					$html .= "<p style='page-break-before: always;'></p>";
				}
			}
			if(($cnt%2) == 0){
				$html = substr($html,0,-42);
			}
			$this->db->close();		
#			$html .= "</body></html>";
			return($html);
		}
	}

	public function generateUnprintedCard($markedPrinted, $printed, $stage, $start = NULL, $end = NULL, $col = 2) {

		$this->db->open();
		$query = sprintf("SELECT o.*, s.scientificName as scientificName FROM occurrence o LEFT OUTER JOIN species_aou s ON o.vernacularName = s.commonName WHERE historicalCard = 0 AND stage = '%s' "
			,	mysql_escape_string($stage)
		);

		if ($start != NULL) {
			$query .= " AND o.id >= " . $start;
		}

		if ($end != NULL) {
			$query .= " AND o.id <= " . $end;
		}

		if ($start == NULL && $end == NULL) {
			$query .= " AND markedPrinted = $printed ";
		}

		$query .= " ORDER BY id";
		
		$result = @mysql_query($query);
		$cnt = 0;
		$html = "<!DOCTYPE HTML><html>"
		. "<head>"
		. "<link href=\"../css/card.css\" rel=\"stylesheet\" type=\"text/css\">"
		. "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />"
		. "<title>3 X 5 Card</title>"
		. "</head>"
		. "<body>";

		if ($col == NULL) $col = 2;
		
		if($result){
			if ($col == 2) {
				$tpl = file_get_contents("../tpl/card-multicol.html");			
			} else {
				$tpl = file_get_contents("../tpl/3cards.html");
			}
			while($row = @mysql_fetch_array($result)){
				$detection = '';
				if($row['audioRec'] == '1')
					$detection = 'Audio Rec, ';
				if($row['heardOnly'] == '1')
					$detection .= 'Heard Only, ';
				if($row['photo'] == '1')
					$detection .= 'Photo, ';
				if($row['sightRec'] == '1')
					$detection .= 'Sight Rec, ';
				if($row['specimen'] == '1')
					$detection .= 'Specimen, ';
				if($row['video'] == '1')
					$detection .= 'Video';

  			$html .= sprintf($tpl
					,	$row['vernacularName']
					,	($row['scientificName'] == '') ? '' : '(' . $row['scientificName'] . ')'
					,	htmlentities($row['sex'], ENT_QUOTES, "UTF-8")
					, htmlentities($row['age'], ENT_QUOTES, "UTF-8")
					,	htmlentities($row['characteristics'], ENT_QUOTES, "UTF-8")
					,	htmlentities($row['numberSeen'], ENT_QUOTES, "UTF-8")
					,	htmlentities($row['county'], ENT_QUOTES, "UTF-8")
					,	htmlentities($row['locality'], ENT_QUOTES, "UTF-8")
					,	date("d M Y", strtotime($row['dateObserved']))
					,	htmlentities($row['verbatimDate'], ENT_QUOTES, "UTF-8")
					,	htmlentities($row['observers'], ENT_QUOTES, "UTF-8")
					,	htmlentities($detection, ENT_QUOTES, "UTF-8")
					,	htmlentities($row['significance'], ENT_QUOTES, "UTF-8")
					,	htmlentities($row['description'], ENT_QUOTES, "UTF-8")
					, $row['id']
					,	date("d M Y")
				);
				$cnt++;
				if(($cnt%$col) == 0)	{
					$html .= "<p style='page-break-before: always;'></p>";
				}
				$this->updateMarkedPrinted($row['id']);
			}

			if(($cnt%$col) == 0){
				$html = substr($html, 0, -42);
			}

			$this->db->close();
			$html .= "</body></html>";
			return($html);
		}

	}

	public function get( $field ) {
		if (isset($this->record[$field])) {
			return( $this->record[$field] );
		} else {
			return( false );
		}
	}

	public function set( $field, $value ) {
		$this->record[$field] = $value;
		return( true );
	}

	public function load_by_id($id) {
		$this->db->open();
		$this->clearRecord();
		$query = sprintf("SELECT * FROM `occurrence` WHERE `id` = '%s' LIMIT 1 ", mysql_escape_string($id) );
		$result = @mysql_query($query);
		if($result){
			$row = @mysql_fetch_assoc($result);
			if(is_array($row) && count($row)) {
				foreach( $row as $field => $value ) {
					$this->set($field, $value);
				}
				$this->db->close();
				return (true);
			}
		}
		$this->db->close();
		return (false);
	}

	public function idExists($id, $originalPath = "") {
		$this->db->open();
		$query = sprintf("SELECT `id` FROM `occurrence` WHERE `id` = '%s' ", mysql_escape_string($id) );
		if ($originalPath != "") {
			$query .= sprintf(" OR originalPath = '%s' ", mysql_escape_string($originalPath) );
		}
		$query .= "LIMIT 1;";
		
		$result = mysql_query($query);
		$this->db->close();
		if(@mysql_num_rows($result)){
			return true;
		}
		return false;
	}

	public function save($overwrite = 0) {
		$query = "";
		if($this->idExists($this->get('id'), $this->get('originalPath') )) {		
			if ($this->get('originalPath') == "" || $overwrite == 1) {
				$query = sprintf("UPDATE `occurrence` SET `vernacularName` = '%s', `numberSeen` = '%s', `sex` = '%s', `age` = '%s', `characteristics` = '%s', `state` = '%s', `dateObserved` = '%s', `verbatimDate` = '%s', `locality` = '%s', `description` = '%s', `stage` = '%s', `lastModified` = NOW(), `observers` = '%s', `contactInfo` = '%s', `imageId` = '%s', `county` = '%s', `significance` = '%s', `audioRec` = '%s', `audioRecUri` = '%s', `heardOnly` = '%s', `photo` = '%s', `photoUri` = '%s', `sightRec` = '%s', `specimen` = '%s', `video` = '%s', `videoUri` = '%s', `auth` = '%s', `filename` = '%s', `path` = '%s', `filename2` = '%s', `path2` = '%s', `filename3` = '%s', `path3` = '%s', `filename4` = '%s', `path4` = '%s', `filename5` = '%s', `path5` = '%s', `markedPrinted` = '%s', `check_out` = '%s', `check_in` = '%s', `historicalCard` = '%s', `originalPath` = '%s', `public` = '%s', modifiedBy = '%s' WHERE `id` = '%s' ;"
				, mysql_escape_string($this->get('vernacularName'))
				, mysql_escape_string($this->get('numberSeen'))
				, mysql_escape_string($this->get('sex'))
				, mysql_escape_string($this->get('age'))
				, mysql_escape_string($this->get('characteristics'))
				, mysql_escape_string($this->get('state'))
				, mysql_escape_string($this->get('dateObserved'))
				, mysql_escape_string($this->get('verbatimDate'))
				, mysql_escape_string($this->get('locality'))
				, mysql_escape_string($this->get('description'))
				, mysql_escape_string($this->get('stage'))
				, mysql_escape_string($this->get('observers'))
				, mysql_escape_string($this->get('contactInfo'))
				, mysql_escape_string($this->get('imageId'))
				, mysql_escape_string($this->get('county'))
				, mysql_escape_string($this->get('significance'))
				, mysql_escape_string($this->get('audioRec'))
				, mysql_escape_string($this->get('audioRecUri'))
				, mysql_escape_string($this->get('heardOnly'))
				, mysql_escape_string($this->get('photo'))
				, mysql_escape_string($this->get('photoUri'))
				, mysql_escape_string($this->get('sightRec'))
				, mysql_escape_string($this->get('specimen'))
				, mysql_escape_string($this->get('video'))
				, mysql_escape_string($this->get('videoUri'))
				, mysql_escape_string($this->get('auth'))
				, mysql_escape_string($this->get('filename'))
				, mysql_escape_string($this->get('path'))
				, mysql_escape_string($this->get('filename2'))
				, mysql_escape_string($this->get('path2'))
				, mysql_escape_string($this->get('filename3'))
				, mysql_escape_string($this->get('path3'))
				, mysql_escape_string($this->get('filename4'))
				, mysql_escape_string($this->get('path4'))
				, mysql_escape_string($this->get('filename5'))
				, mysql_escape_string($this->get('path5'))
				, mysql_escape_string($this->get('markedPrinted'))
				, mysql_escape_string($this->get('check_out'))
				, mysql_escape_string($this->get('check_in'))
				, mysql_escape_string($this->get('historicalCard'))
				, mysql_escape_string($this->get('originalPath'))
				, mysql_escape_string($this->get('public'))
				, mysql_escape_string($this->get('modifiedBy'))
				, mysql_escape_string($this->get('id'))
				);
			}
		} else {
			$query = sprintf("INSERT INTO `occurrence` SET `vernacularName` = '%s', `numberSeen` = '%s', `sex` = '%s', `age` = '%s', `characteristics` = '%s', `state` = '%s', dateObserved = '%s', `verbatimDate` = '%s', `locality` = '%s', `description` = '%s', `stage` = '%s', `lastModified` = NOW(), `observers` = '%s', `contactInfo` = '%s', `imageId` = '%s', `county` = '%s', `significance` = '%s', `audioRec` = '%s', `audioRecUri` = '%s', `heardOnly` = '%s', `photo` = '%s', `photoUri` = '%s', `sightRec` = '%s', `specimen` = '%s', `video` = '%s', `videoUri` = '%s', `auth` = '%s', `filename` = '%s', `path` = '%s', `filename2` = '%s', `path2` = '%s', `filename3` = '%s', `path3` = '%s', `filename4` = '%s', `path4` = '%s', `filename5` = '%s', `path5` = '%s', `markedPrinted` = '%s', `check_out` = '%s', `check_in` = '%s', `historicalCard` = '%s', `originalPath` = '%s', `public` = '%s', `modifiedBy` = '%s' ;"
				, mysql_escape_string($this->get('vernacularName'))
				, mysql_escape_string($this->get('numberSeen'))
				, mysql_escape_string($this->get('sex'))
				, mysql_escape_string($this->get('age'))
				, mysql_escape_string($this->get('characteristics'))
				, mysql_escape_string($this->get('state'))
				, mysql_escape_string($this->get('dateObserved'))
				, mysql_escape_string($this->get('verbatimDate'))
				, mysql_escape_string($this->get('locality'))
				, mysql_escape_string($this->get('description'))
				, mysql_escape_string($this->get('stage'))
				, mysql_escape_string($this->get('observers'))
				, mysql_escape_string($this->get('contactInfo'))
				, mysql_escape_string($this->get('imageId'))
				, mysql_escape_string($this->get('county'))
				, mysql_escape_string($this->get('significance'))
				, mysql_escape_string($this->get('audioRec'))
				, mysql_escape_string($this->get('audioRecUri'))
				, mysql_escape_string($this->get('heardOnly'))
				, mysql_escape_string($this->get('photo'))
				, mysql_escape_string($this->get('photoUri'))
				, mysql_escape_string($this->get('sightRec'))
				, mysql_escape_string($this->get('specimen'))
				, mysql_escape_string($this->get('video'))
				, mysql_escape_string($this->get('videoUri'))
				, mysql_escape_string($this->get('auth'))
				, mysql_escape_string($this->get('filename'))
				, mysql_escape_string($this->get('path'))
				, mysql_escape_string($this->get('filename2'))
				, mysql_escape_string($this->get('path2'))
				, mysql_escape_string($this->get('filename3'))
				, mysql_escape_string($this->get('path3'))
				, mysql_escape_string($this->get('filename4'))
				, mysql_escape_string($this->get('path4'))
				, mysql_escape_string($this->get('filename5'))
				, mysql_escape_string($this->get('path5'))
				, mysql_escape_string($this->get('markedPrinted'))
				, mysql_escape_string($this->get('check_out'))
				, mysql_escape_string($this->get('check_in'))
				, mysql_escape_string($this->get('historicalCard'))
				, mysql_escape_string($this->get('originalPath'))
				, mysql_escape_string($this->get('public'))
				, mysql_escape_string($this->get('modifiedBy'))
			);
		}
		
		if ($query != "") {
			$this->db->open();
			$result = @mysql_query($query);
			if(@strpos($query, 'INSERT INTO') !== false) {
				$this->set("id", @mysql_insert_id() );
			}
			$this->db->close();
			return $result;
		} else {
			return false;
		}
	}

	public function load_by_filter($filter){
		$where = buildWhere($filter['filter']);
		if ($where != '') {
			$where = " WHERE " . $where;
		}
		$query = 'SELECT * FROM `occurrence` ';
		if($filter['field'] != '' &&  $filter['value'] !== '') {
			$where .= sprintf(" AND %s = '%s' ;"
			, mysql_escape_string($filter['field'])
			, mysql_escape_string($filter['value'])
			);
		}
		$query .= $where;
		$data = array();
		$this->db->open();
		$result = mysql_query($query);
		if($result) {
			while($row = mysql_fetch_object($result)) {
				$data[] = $row;
			}
		}
		$this->db->close();
		return $data;
	}

	public function clearRecord() {
		unset($this->record);
		return true;
	}

	public function getImage($id) {
		$this->db->open();
		$query = sprintf("SELECT path, filename FROM occurrence WHERE id = '%s'"
			,	mysql_escape_string($id)
		);

		$result = @mysql_query($query);
     	if (!empty($result)) {
     		$path = mysql_fetch_array($result);
     		$posts = $path[0].$path[1];
     		return $posts;
     	} else {
     		return (false);
     	}
	}

	public function getCount() {
		return ($this->totalCount);
	}

	private function findImageSlot() {
		if ($this->get("filename2") == "" && $this->get("path2") == "") return(2);
		if ($this->get("filename3") == "" && $this->get("path3") == "") return(3);
		if ($this->get("filename4") == "" && $this->get("path4") == "") return(4);
		if ($this->get("filename5") == "" && $this->get("path5") == "") return(5);
		return(false);
	}
	
  public function linkImage($id) {
		$this->db->open();
		$query = sprintf("SELECT * FROM `occurrence` where id = (SELECT max(id) from `occurrence` WHERE historicalCard = 1 AND deleted = 0 AND id < %s)"
			,	mysql_escape_string($id)
		);
		$result = @mysql_query($query);
		
		if (!empty($result)) {
			$data = mysql_fetch_array($result);
			$maxId = $data['id'];
			
			$this->load_by_id($id);
			$tmpFilename = $this->get("filename");
			$tmpPath = $this->get("path");
			$tmpFilename2 = $this->get("filename2");
			$tmpPath2 = $this->get("path2");
			$tmpFilename3 = $this->get("filename3");
			$tmpPath3 = $this->get("path3");
			$tmpFilename4 = $this->get("filename4");
			$tmpPath4 = $this->get("path4");
			$tmpFilename5 = $this->get("filename5");
			$tmpPath5 = $this->get("path5");
			
			$this->load_by_id($maxId);
			$found = false;
			if ($tmpFilename != "" && $tmpPath != "") {
				$slotIndex = $this->findImageSlot();
				if ($slotIndex) {
					$this->set("filename" . $slotIndex, $tmpFilename);
					$this->set("path" . $slotIndex, $tmpPath);				
					$found = true;
				}
			}

			if ($tmpFilename2 != "" && $tmpPath2 != "") {
				$slotIndex = $this->findImageSlot();
				if ($slotIndex) {
					$this->set("filename" . $slotIndex, $tmpFilename2);
					$this->set("path" . $slotIndex, $tmpPath2);				
					$found = true;
				}
			}

			if ($tmpFilename3 != "" && $tmpPath3 != "") {
				$slotIndex = $this->findImageSlot();
				if ($slotIndex) {
					$this->set("filename" . $slotIndex, $tmpFilename3);
					$this->set("path" . $slotIndex, $tmpPath3);				
					$found = true;
				}
			}

			if ($tmpFilename4 != "" && $tmpPath4 != "") {
				$slotIndex = $this->findImageSlot();
				if ($slotIndex) {
					$this->set("filename" . $slotIndex, $tmpFilename4);
					$this->set("path" . $slotIndex, $tmpPath4);				
					$found = true;
				}
			}

			if ($tmpFilename5 != "" && $tmpPath5 != "") {
				$slotIndex = $this->findImageSlot();
				if ($slotIndex) {
					$this->set("filename" . $slotIndex, $tmpFilename5);
					$this->set("path" . $slotIndex, $tmpPath5);				
					$found = true;
				}
			}

			if ($found) {
				$this->db->open();
				$delQuery = sprintf("UPDATE `occurrence` SET `deleted` = '1', check_in = '%s', modifiedBy = '%s' WHERE id = '%s';"
					,	@date('Y-m-d H:i:s')
					,	$_SESSION['user']
					,	mysql_escape_string($id)
				);
				@mysql_query($delQuery);
				$this->save(1);
				return(true);
			} else {
				return(false);
			}

		}
	}

/*
	public function setAuth($id, $auth) {
		$this->db->open();
		$query = sprintf("UPDATE `occurrence` set auth = '%s' WHERE id = '%s'"
			, mysql_escape_string($auth)
			, mysql_escape_string($id)
		);
		@mysql_query($query);
	}
*/

}
?>